package com.lagou.homework.dao;

import com.lagou.homework.bean.Student;
import com.lagou.homework.util.DbUtil;

import java.sql.*;

/**
 * StudentManage接口的实现类，用于实现与数据库的交互的具体方法
 */
public class StudentManageDaoImp implements StudentManageDao {

    /**
     * 重写获取所有学生信息的方法
     * @return
     */
    @Override
    public Student[] getAllStudents() {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 1.准备数据库连接
            connection = DbUtil.getConnection();
            // 2.准备sql语句，创建预处理对象
            String sql = "SELECT * FROM t_student";
//          PreparedStatement ps = connection.prepareStatement(sql) 不需要预处理
            statement = connection.createStatement();
            // 3.设置占位符，执行sql语句并获取结果集对象
            resultSet = statement.executeQuery(sql);
            // 4.创建Student[]对象，从结果集中获取数据
            // 将指针移动到最后一条数据，获取数据的总条数
            resultSet.last();
            int rowCount = resultSet.getRow();
            Student[] allStudents = new Student[rowCount];
            // 将指针移动到第一条数据，并给数组的第一个元素赋值
            resultSet.first();
            allStudents[0] = new Student(resultSet.getInt("code"), resultSet.getString("userName"),
                    resultSet.getString("sex"), resultSet.getString("birthday"),
                    resultSet.getString("email"), resultSet.getString("note"));
            // 使用while循环给数组的剩余元素赋值
            int i = 1;
            while (resultSet.next()) {
                allStudents[i] = new Student(resultSet.getInt("code"), resultSet.getString("userName"),
                        resultSet.getString("sex"), resultSet.getString("birthday"),
                        resultSet.getString("email"), resultSet.getString("note"));
                i++;
            }
            return allStudents; // 返回数组对象
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 5.关闭相关资源
            try {
                DbUtil.close(connection, statement, resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return new Student[0]; // 获取失败返回空数组
    }

    /**
     * 重写添加学生信息的方法
     * @param student
     * @return
     */
    @Override
    public int addStudent(Student student) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            // 1.准备数据库连接
            connection = DbUtil.getConnection();
            // 2.准备sql语句以及预处理对象
            String sql = "INSERT INTO t_student VALUES(?, ?, ?, ?, ?, ?)";
            ps = connection.prepareStatement(sql);
            // 3.设置占位符，执行sql语句，获取返回值（受影响的行数）
            ps.setInt(1, student.getCode()); // 设置学号
            ps.setString(2, student.getUserName()); // 设置姓名
            ps.setString(3, student.getSex()); // 设置性别
            ps.setString(4, student.getBirthday()); // 设置生日
            ps.setString(5, student.getEmail()); // 设置邮箱
            ps.setString(6, student.getNote()); // 设置备注
            int i = ps.executeUpdate();
            return i; // 添加成功返回i = 1
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 4.关闭相关资源
            try {
                DbUtil.close(connection, ps);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return 0; // 添加失败返回0
    }

    /**
     * 重写修改学生信息的方法
     * @param student
     * @return
     */
    @Override
    public int editStudent(Student student) {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            // 1.准备数据库连接
            connection = DbUtil.getConnection();
            // 2.准备sql语句以及预处理对象
            String sql = "UPDATE t_student SET userName = ?, sex = ?, birthday = ?, email = ?, note = ? WHERE code = ?";
            ps = connection.prepareStatement(sql);
            // 3.设置占位符，执行语句，获取返回值并返回
            ps.setString(1, student.getUserName()); // 修改姓名
            ps.setString(2, student.getSex()); // 修改性别
            ps.setString(3, student.getBirthday()); // 修改生日
            ps.setString(4, student.getEmail()); // 修改邮箱
            ps.setString(5, student.getNote()); // 修改备注
            ps.setInt(6, student.getCode()); // 设置学号（作为参考不能修改）
            int i = ps.executeUpdate();
            return i; // 若成功修改返回i = 1
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 4.关闭相关资源
            try {
                DbUtil.close(connection, ps);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return 0; // 若修改失败返回0
    }

    /**
     * 重写删除学生信息的方法
     * @param code
     * @return
     */
    @Override
    public int delStudent(int code) {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            // 1.准备数据库连接
            connection = DbUtil.getConnection();
            // 2.准备sql语句以及预处理对象
            String sql = "DELETE FROM t_student WHERE code = ?";
            ps = connection.prepareStatement(sql);
            // 3.设置占位符，执行语句,获取返回值并返回
            ps.setInt(1, code);
            int i = ps.executeUpdate();
            return i; // 若成功返回i = 1
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 4.关闭相关资源
            try {
                DbUtil.close(connection, ps);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return 0; // 若失败返回0
    }

    /**
     * 重写查询学生信息的方法
     * @param code
     * @return
     */
    @Override
    public Student searchStudent(int code) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            // 1.准备数据库连接
            connection = DbUtil.getConnection();
            // 2.准备sql语句和预处理对象
            String sql = "SELECT * FROM t_student WHERE code = ?";
            ps = connection.prepareStatement(sql);
            // 3.设置占位符，执行查询，根据查询结果创建Student对象，返回对象
            ps.setInt(1, code);
            rs = ps.executeQuery();
            rs.first();
            Student student = new Student(rs.getInt("code"), rs.getString("userName"),
            rs.getString("sex"), rs.getString("birthday"), rs.getString("email"),
            rs.getString("note"));
            return student;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 4.关闭相关资源
            try {
                DbUtil.close(connection, ps, rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return new Student(0, "", "", "", "", "未找到该学生"); // 若查询失败返回该学生对象
    }
}
